package com.student.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.springframework.stereotype.Component;

import com.student.dao.ProfessionRegisterPaymentDao;
import com.student.model.Colleges;
import com.student.model.Profession;
import com.student.model.Student;
import com.student.util.PageInfo;

@Component("ProfessionRegisterPaymentDao")
public class ProfessionRegisterPaymentDaoImpl extends HibernateDaoImpl
		implements ProfessionRegisterPaymentDao {

	public List<Profession> getAllProfession(Long collegeId) throws Exception {
		try {
			String hql = "From Profession p where p.colleges.collegeId='"
					+ collegeId + "'";
			return this.find(hql);
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e);
		}
	}

	public Integer getStudentCount(Long professionId, Integer clazz,
			Boolean isRegister, Boolean sex) throws Exception {
		try {
			String hql = "SELECT COUNT(*) FROM Student s where 1=1";
			if (professionId != null) {
				hql += " AND s.profession.professionId='" + professionId + "'";
			}
			if (clazz != null) {
				hql += " AND s.clazz='" + clazz + "'";
			}
			if (isRegister != null) {
				hql += " AND s.isRegister='" + isRegister + "'";
			}
			if (sex != null) {
				hql += " AND s.sex='" + sex + "'";
			}
			Query query = getSession().createQuery(hql);
			if (query.iterate().next() != null) {
				return new Integer(query.iterate().next() + "");
			}
			return 0;
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e);
		}
	}

	public Integer getStudentCount(String campusName,Long collegesId, Long professionId,
			Boolean isRegister, Boolean sex, String subjects, String area)
			throws Exception {
		try {
			String hql = "SELECT COUNT(*) FROM Student s WHERE 1=1";
			if (campusName != null) {
				hql += " AND s.profession.colleges.campus.campusName='" + campusName
				+ "'";
			}
			if (collegesId != null) {
				hql += " AND s.profession.colleges.collegeId='" + collegesId
						+ "'";
			}
			if (professionId != null) {
				hql += " AND s.profession.professionId='" + professionId + "'";
			}
			if (isRegister != null) {
				hql += " AND s.isRegister='" + isRegister + "'";
			}
			if (sex != null) {
				hql += " AND s.sex='" + sex + "'";
			}
			if (subjects != null) {
				hql += " AND s.subjects.subjectsName='" + subjects + "'";
			}
			if (area != null) {
				hql += " AND s.area.areaName='" + area + "'";
			}
			Query query = getSession().createQuery(hql);
			if (query.iterate().next() != null) {
				return new Integer(query.iterate().next() + "");
			}
			return 0;
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e);
		}
	}

	public List<Colleges> getAllCollege() throws Exception {
		String hql = "FROM Colleges c ORDER BY c.collegeId";
		return this.find(hql);
	}

	public Double getTotalPaymentAmount(Long collegeId, Long professionId,
			Long studentId, Boolean isRegister) throws Exception {
		try {
			String hql = "SELECT SUM(p.feePayable) FROM Payment p WHERE 1=1";
			if (collegeId != null) {
				hql += " AND p.student.profession.colleges.collegeId='"
						+ collegeId + "'";
			}
			if (professionId != null) {
				hql += " AND p.student.profession.professionId='"
						+ professionId + "'";
			}
			if (studentId != null) {
				hql += " AND p.student.studentId='" + studentId + "'";
			}
			if (isRegister != null) {
				hql += " AND p.student.isRegister='" + isRegister + "'";
			}
			Query query = getSession().createQuery(hql);
			if (query.iterate().next() != null) {
				return new Double(query.iterate().next() + "");
			}
			return 0.0;
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e);
		}
	}

	public Double getTotalPaymentAmount(Long professionId, Integer clazz,
			Boolean isRegister) throws Exception {
		try {
			String hql = "SELECT SUM(p.feePayable) FROM Payment p WHERE 1=1";
			if (professionId != null) {
				hql += " AND p.student.profession.professionId='"
						+ professionId + "'";
			}
			if (clazz != null) {
				hql += " AND p.student.clazz='" + clazz + "'";
			}
			if (isRegister != null) {
				hql += " AND p.student.isRegister='" + isRegister + "'";
			}
			Query query = getSession().createQuery(hql);
			if (query.iterate().next() != null) {
				return new Double(query.iterate().next() + "");
			}
			return 0.0;
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e);
		}
	}

	public Double getTotalPaidAmount(Long collegeId, Long professionId,
			Long studentId, Boolean isRegister) throws Exception {
		try {
			String hql = "SELECT SUM(p.feePayable-p.unpaidFees) FROM Payment p WHERE 1=1";
			if (collegeId != null) {
				hql += " AND p.student.profession.colleges.collegeId='" + collegeId
				+ "'";
			}
			if (professionId != null) {
				hql += " AND p.student.profession.professionId='" + professionId
				+ "'";
			}
			if (studentId != null) {
				hql += " AND p.student.studentId='" + studentId + "'";
			}
			if (isRegister != null) {
				hql += " AND p.student.isRegister='" + isRegister + "'";
			}
			Query query = getSession().createQuery(hql);
			if (query.iterate().next() != null) {
				return new Double(query.iterate().next() + "");
			}
			return 0.0;

		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e);
		}
	}

	public Double getTotalPaidAmount(Long professionId, Integer clazz,
			Boolean isRegister) throws Exception {
		try {

			String hql = "SELECT SUM(p.feePayable-p.unpaidFees) FROM Payment p WHERE 1=1";
			if (professionId != null) {
				hql += " AND p.student.profession.professionId='"
						+ professionId + "'";
			}
			if (clazz != null) {
				hql += " AND p.student.clazz='" + clazz + "'";
			}
			if (isRegister != null) {
				hql += " AND p.student.isRegister='" + isRegister + "'";
			}
			Query query = getSession().createQuery(hql);
			if (query.iterate().next() != null) {
				return new Double(query.iterate().next() + "");
			}
			return 0.0;
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e);
		}
	}

	public Profession getProfessionById(Long professionId) throws Exception {
		try {
			if (professionId == null)
				return null;
			String hql = "FROM Profession p where p.professionId='"
					+ professionId + "'";
			List list = this.find(hql);
			if (list == null || list.size() <= 0)
				return null;
			return (Profession) list.get(0);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e);
		}
	}

}
